# -*- coding: utf-8 -*-
# @Time    : 2022/11/16 15:07
# @Author  : yangyuexiong
# @Email   : yang6333yyx@126.com
# @File    : main.py
# @Software: PyCharm

import pathlib

from openpyxl import load_workbook, Workbook

"""
单元格:cell
一行:a_line
一列:one_column
全表:all
自定义区间:custom_range
"""
read_range_tuple = ('cell', 'a_line', 'one_column', 'all', 'custom_range')
write_range_tuple = ('cell', 'a_line', 'one_column', 'custom_range', 'write_coordinate')
del_range_tuple = ('a_line', 'one_column', 'all')
clear_range_tuple = ('cell', 'a_line', 'one_column', 'all', 'custom_range')
insert_range_tuple = ('a_line', 'one_column')
sort_range_tuple = ('custom_range',)


class ExcelHandle:
    """excel操作"""

    def __init__(self, excel_path=None, debug=None):
        self.debug = debug
        self.excel_path = excel_path
        self.sheet_list = []
        self.sheet_obj_list = []
        self.wb = None
        self.throw = {
            "status": True,
            "message": "操作成功",
            "data": ""
        }

    def return_error_file(self):
        """通用打开文件异常"""

        msg = f'打开:excel文件失败:{self.excel_path}'
        print(msg)
        self.throw['status'] = False
        self.throw['message'] = msg

    def cell_to_json(self, cell):
        """
        生成cell数据json对象
        :param cell: cell对象
        :return:
        """

        coordinate = cell.coordinate
        value = cell.value

        if not isinstance(value, (str, int, dict, list)):
            value = str(value)
        data = {
            "coordinate": coordinate,
            "value": value,
        }
        return data

    def set_cell_none(self, cell):
        """
        设cell值为空
        :param cell: cell对象
        :return:
        """

        print(cell)
        print(f"元数据:{cell.value}")
        try:
            cell.value = ''
        except BaseException as e:
            print(e)
            print(cell.coordinate, cell.value)

    def excel_create(self, file_name, is_cover=False):
        """
        创建文件
        :param file_name: 文件名称
        :param is_cover: 是否覆盖原有同名的文件
        :return:
        """

        try:
            new_excel_path = self.excel_path + f'/{file_name}.xlsx'
            print(new_excel_path)
            check_file = pathlib.Path(new_excel_path).exists()
            print(f"check_file:{check_file}")
            if not check_file:
                wb = Workbook()
                wb.save(new_excel_path)
                self.wb = load_workbook(new_excel_path)
                print(f"new wb:{self.wb}")
                self.sheet_list = self.wb.sheetnames
                print(f"sheet_list:{self.sheet_list}")
            else:
                print(f'is_cover:{is_cover}')
                if is_cover:
                    wb = Workbook()
                    wb.save(new_excel_path)
                    self.wb = load_workbook(new_excel_path)
                    print(f"new wb cover:{self.wb}")
                    self.sheet_list = self.wb.sheetnames
                    print(f"sheet_list:{self.sheet_list}")
                else:
                    self.wb = load_workbook(new_excel_path)
                    print(f"old wb:{self.wb}")
                    self.sheet_list = self.wb.sheetnames
                    print(f"sheet_list:{self.sheet_list}")

            return True
        except BaseException as e:
            self.return_error_file()
            return self.throw

    def excel_open(self, read_only=False, data_only=False):
        """打开文件"""

        try:
            self.wb = load_workbook(self.excel_path, read_only=read_only, data_only=data_only)
            print(f"wb:{self.wb}")
            self.sheet_list = self.wb.sheetnames
            print(f"sheet_list:{self.sheet_list}")
            self.sheet_obj_list = [{"sheet_name": name} for name in self.sheet_list]
            print(f"sheet_obj_list:{self.sheet_obj_list}")
            return True
        except BaseException as e:
            print(e)
            self.return_error_file()
            return self.throw

    def get_sheet_list(self):
        """返回sheet_list"""
        return self.sheet_list

    def get_sheet_obj_list(self):
        """返回sheet_obj_list"""
        return self.sheet_obj_list

    def check_sheet_name(self, sheet_name):
        """
        检验sheet是否存在
        :param sheet_name:
        :return:
        """

        if sheet_name not in self.sheet_list:
            msg = f'sheet名称: {sheet_name} 不存在'
            print(msg)
            self.throw['status'] = False
            self.throw['message'] = msg
            return False
        return True

    def get_sheet(self, sheet_name):
        """
        获取sheet对象
        :param sheet_name:
        :return:
        """

        sheet = self.wb[sheet_name]
        return sheet

    def excel_save_as(self, is_save_as=None, new_path=None):
        """

        :param is_save_as: 是否另存为
        :param new_path: 另存为新的路径
        :return:
        """

        if is_save_as:
            # print(f'另存为：{new_path}')
            self.wb.save(new_path)
        else:
            # print(f'保存原文件：{self.excel_path}')
            self.wb.save(self.excel_path)

    def excel_save(self):
        """保存"""

        try:
            if isinstance(self.wb, Workbook):
                self.wb.save(self.excel_path)
                print(f"保存:{self.excel_path.split('/')[-1]}")
        except BaseException as e:
            self.return_error_file()
            return self.throw

    def excel_close(self):
        """关闭文件"""

        try:
            if isinstance(self.wb, Workbook):
                self.wb.close()
                print(f"关闭:{self.excel_path.split('/')[-1]}")
                return True
        except BaseException as e:
            self.return_error_file()
            return self.throw
